# -*- coding: utf-8 -*-

from django.db import models

# Create your models here.
INTER_CHOICES = (
('0', "NC"),
('1', 'HI'),
('2', 'SI'),
('3', 'NI'),
('4', 'NT'),
('5', 'LI'),
)
EDUCATION_CHOICES = (
('1', 'Secondary School'),
('2', 'Diploma'),
('3', "Bachelor's Degree"),
('4', "Master's Degree"),
('5', 'Ph.D.'),
)
STATUS_CHOICES = (
('0', "Not yet"),
('1', "Can't reach"),
('2', "App sch'd"),
('3', "Under cons"),
('4', "Call later"),
('5', 'Conflict'),
('6', 'No call'),
('7', 'Enrolled'),
)

#销售人员信息表
class Salesperson(models.Model):
    name = models.CharField(max_length = 20, unique = True)
    truename = models.CharField(max_length = 50)
    tel = models.CharField(max_length = 20)
    mobile = models.CharField(max_length = 20)
    sendsms = models.IntegerField(max_length = 2, default = 0)
    email = models.CharField(max_length = 100)
    password = models.CharField(max_length = 128)
    level = models.CharField(max_length = 20)
    group = models.IntegerField(max_length = 2) #add sales group huangzy edit

    def __unicode__(self):
        return self.name

class Role(models.Model):  #add user role table
    name = models.CharField(max_length = 20, unique = True)
    role_ids = models.CharField(max_length = 2000)
    upd_date = models.DateTimeField()
    upd_user = models.CharField(max_length = 20)
    status = models.IntegerField(max_length = 2, default = 1)

class Salesperson_groups(models.Model):  #add sales group table
    name = models.CharField(max_length = 20, unique = True)
    code = models.CharField(max_length = 20)
    chinese = models.CharField(max_length = 10)
    status = models.IntegerField(max_length = 2, default = 1)

class Agent(models.Model):
    name = models.CharField(max_length = 20)
    intro = models.CharField(max_length = 200)
    budget = models.IntegerField(max_length = 200)

    def __unicode__(self):
        return self.name

class Furtherinterest(models.Model):
    name = models.CharField(max_length = 20)

#项目表
class Projects(models.Model):
    name = models.CharField(max_length = 100)
    intro = models.CharField(max_length = 200)
    imvalue = models.SmallIntegerField(max_length=2,default=0)
    position = models.SmallIntegerField(max_length=2,default=0)
#    name_ch = models.CharField(max_length = 30)
#    fee = models.FloatField(max_length = 10)
#    currency = models.CharField(max_length = 10)
#    pay_times =  models.IntegerField(max_length = 2)
#    latest = models.IntegerField(max_length = 5)
    
    def __unicode__(self):
        return self.name

#客户信息表
class Members(models.Model):
    salesperson = models.ForeignKey(Salesperson, null = True)
    interlevel = models.CharField(max_length = 1, choices = INTER_CHOICES)
    name = models.CharField(max_length = 30)
    job_title = models.CharField(max_length = 200)
    company = models.CharField(max_length = 200)
    tel_h = models.CharField(max_length = 20)
    tel_o = models.CharField(max_length = 20)
    tel_m = models.CharField(max_length = 20)
    email = models.CharField(max_length = 100)
    address = models.CharField(max_length = 200)
    regtime = models.DateTimeField()
    received_emaill = models.BooleanField(default = 0)
    received_hardcopy = models.BooleanField(default = 0)
    education = models.CharField(max_length = 1, choices = EDUCATION_CHOICES)
    work_experience = models.CharField(max_length = 30)
    agent = models.ForeignKey(Agent, default = 1)
    furtherinterest = models.ForeignKey(Furtherinterest, null = True)
    projects = models.ForeignKey(Projects, null = True)
    status = models.CharField(max_length = 20, choices = STATUS_CHOICES)
    attendsess = models.CharField(max_length = 10, default = 'N')
    last_contact = models.DateTimeField()
    last_allocate = models.DateTimeField(null = True)
    mba = models.SmallIntegerField(max_length = 1, default = 0)
    mpa = models.SmallIntegerField(max_length = 1, default = 0)
    dba = models.SmallIntegerField(max_length = 1, default = 0)
    message = models.TextField()
    samecode = models.CharField(max_length = 20,default = '')
    email_state = models.SmallIntegerField(max_length = 1, default = 1)
    men_area = models.SmallIntegerField(max_length = 1, default = 1)
    men_ip = models.CharField(max_length = 15,default = '')
    is_removed = models.SmallIntegerField(max_length = 1, default = 0)
    def __unicode__(self):
        return self.name

#用于记录销售对客户跟进情况
class Follow_up(models.Model):
    members = models.ForeignKey(Members)
    status = models.CharField(max_length = 20, choices = STATUS_CHOICES)
    remarks = models.TextField()
    timeline = models.DateField(auto_now_add = True)

#记录下次对客户的跟进时间
class Next_Follow_Date(models.Model):
    member = models.ForeignKey(Members)
    follow_date = models.DateField(null = True)
    remark = models.TextField()
    is_deal = models.SmallIntegerField(max_length = 1, default = 0)
    is_removed = models.SmallIntegerField(max_length = 1, default = 0)
    class Meta:
        db_table = 'next_follow_date'

#客户提交报名意向后自动发送的邮件信息[目前很少用到]
class Wellcome_mail(models.Model):
    program = models.ForeignKey(Projects)
    title = models.CharField(max_length = 200)
    maincontent = models.TextField()
    thanks = models.CharField(max_length = 200)
    address = models.TextField()
    site = models.CharField(max_length = 200)
    sales = models.ForeignKey(Salesperson)
    autosendmail = models.IntegerField(max_length = 2, default = 1)
    fileurl = models.CharField(max_length = 100, null = True)

#各个项目进来的客户分配给销售跟进的人数比例.
class Project_sales_rate(models.Model):
    project_id = models.IntegerField(max_length = 20)
    salesperson_id = models.IntegerField(max_length = 20)
    rate = models.IntegerField(max_length = 20)
    group_id = models.IntegerField(max_length = 20)

#记录客项目已分配给各销售的总数.与上表结合方便准确按比例把进来的客户自动分配给销售
class Project_allocate(models.Model):
    project_id = models.IntegerField(max_length = 20)
    salesperson_id = models.IntegerField(max_length = 20)
    sum = models.IntegerField(max_length = 20)
    group_id = models.IntegerField(max_length = 20)

#销售对应客户的记录(因中途可能会更销售,因为需此表记录每个客户有哪些销售跟进过)
class Sales_record(models.Model):
    members = models.ForeignKey(Members, default = '0')
    salesperson = models.ForeignKey(Salesperson, default = '0')
    starttime = models.CharField(max_length = 20, null = True)
    endtime = models.CharField(max_length = 20, null = True)


#商端邮
class gMaillog(models.Model):
    maillist_id = models.IntegerField(max_length = 10, default = 0)
    gmaillist_id = models.IntegerField(max_length = 10, default = 0)
    letterid = models.IntegerField(max_length = 10, default = 0)
    gletter_id = models.IntegerField(max_length = 10, default = 0)
    mail_object = models.CharField(max_length = 100, null = True)
    mail_issend = models.IntegerField(max_length = 2, default = 0)
    mail_sendid = models.CharField(max_length = 13, default = 0)
    mail_sendtime = models.DateTimeField(null = True)
    mail_senduser = models.CharField(max_length = 16, null = True)
    is_removed = models.IntegerField(max_length = 2, default = 0)

#商端邮
class gMailList(models.Model):
    maillist_id = models.IntegerField(max_length = 10, default = 0)
    maillist_name = models.CharField(max_length = 20, default = 0)
    mailfile_path = models.CharField(max_length = 100, default = 0)
    mail_import = models.IntegerField(max_length = 2, default = 0)
    mail_addtime = models.DateTimeField(null = True)
    mail_senduser = models.CharField(max_length = 16, default = 0)
    is_removed = models.IntegerField(max_length = 2, default = 0)

#商端邮
class gMailLetter(models.Model):
    htmlurl = models.CharField(max_length = 500, default = 0)
    lettername = models.CharField(max_length = 30, null = True)
    letterid = models.IntegerField(max_length = 10, default = 0)
    lettercon = models.TextField()
    letter_import = models.IntegerField(max_length = 2, default = 0)
    addtime = models.DateTimeField(null = True)
    adduser = models.CharField(max_length = 16, null = True)
    is_removed = models.IntegerField(max_length = 2, default = 0)

#smtp_mail 联系人组邮件列表
class smtpsendmaligroup(models.Model):
    group_name = models.CharField(max_length = 40, default = 0)
    add_time = models.DateTimeField(null = True)
    add_user = models.CharField(max_length = 20, default = 0)
    is_removed = models.IntegerField(max_length = 2, default = 0)

#smtp_mail 邮件发送日志mailgroup_id = models.IntegerField(max_length = 10, default = 0)
class smtplog(models.Model):
    mailgroup = models.ForeignKey(smtpsendmaligroup, null = True)
    mail_object = models.CharField(max_length = 100, null = True)
    mail_content = models.TextField()
    mail_senduser = models.CharField(max_length = 100, default = 0)
    mail_sendtime = models.DateTimeField(null = True)
    is_removed = models.IntegerField(max_length = 2, default = 0)

#smtp_mail 邮件发送列表状态
class smtpmaillog(models.Model):
    mail_sendmail = models.CharField(max_length = 100, default = 0)
    user_name = models.CharField(max_length = 100, default = 0)
    send_state = models.IntegerField(max_length = 2, default = 1)
    send_id = models.IntegerField(max_length = 11, default = 0)
    mail_sendermail = models.CharField(max_length = 100, default = 0)
    fail_reason = models.CharField(max_length = 1000, default = 0)
    is_removed = models.IntegerField(max_length = 2, default = 0)

#smtp_mail 接收邮件列表
class smtpsendmalilist(models.Model):
    mail_sendmail = models.CharField(max_length = 100, default = 0)
    user_name = models.CharField(max_length = 100, default = 0)
    send_state = models.IntegerField(max_length = 2, default = 2)
    mailgroup_id = models.IntegerField(max_length = 10, default = 0)
    comefrom = models.CharField(max_length = 20, default = 0)
    comefromcode = models.CharField(max_length = 20, default = 0)
    is_removed = models.IntegerField(max_length = 2, default = 0)


#smtp_mail 发送都邮件列表，用于循环实现多用户发送，一人一天最多100封
class smtpsendermaillist(models.Model):
    emailadd = models.CharField(max_length = 100, default = 0)
    emailpwd = models.CharField(max_length = 26, default = 0)
    emailname = models.CharField(max_length = 106, default = 0)
    smtp_server = models.CharField(max_length = 26, default = 0)
    sendtimes = models.IntegerField(max_length = 10, default = 0)
    daytimes = models.IntegerField(max_length = 10, default = 0)
    daylimit = models.IntegerField(max_length = 10, default = 0)
    senddate = models.DateField(null = True)
    group = models.IntegerField(max_length = 2, default = 0)
    is_removed = models.IntegerField(max_length = 2, default = 0)
    status = models.IntegerField(max_length = 1, default = 1)
    

#邮件
class Maillist(models.Model):
    members = models.ForeignKey(Members, default = '0')
    sales = models.ForeignKey(Salesperson, default = '0')
    addtime = models.DateTimeField(null = True)
    sendtime = models.DateTimeField(null = True)
    tomail = models.CharField(max_length = 100, null = True)
    subject = models.CharField(max_length = 100, null = True)
    mailbody = models.TextField()
    issend = models.IntegerField(max_length = 2, default = 0)
    class Meta:
        db_table = 'maillist'

#邮件销售发送记录.
class Maillog(models.Model):
    members = models.ForeignKey(Members, default = '0')
    sales = models.ForeignKey(Salesperson, default = '0')
    addtime = models.DateTimeField(null = True)
    sendtime = models.DateTimeField(null = True)
    tomail = models.CharField(max_length = 100, null = True)
    subject = models.CharField(max_length = 100, null = True)
    mailbody = models.TextField()
    dontsend = models.IntegerField(max_length = 2, default = 0)#when value is 1, not success to send
    automail = models.IntegerField(max_length = 2, default = 0)
    class Meta:
        db_table = 'maillog'

class Mailfile(models.Model):
    sales = models.ForeignKey(Salesperson, null = True)
    project_id = models.IntegerField(max_length = 11, default = '0')
    title = models.CharField(max_length = 100, null = True)
    fileurl = models.CharField(max_length = 100, null = True)
    automail = models.IntegerField(max_length = 2, default = 0)
    class Meta:
        db_table = 'mailfile'

class Project_course_info(models.Model):
    project = models.ForeignKey(Projects, default = '0')
    period = models.CharField(max_length = 20, null = True, default = '')
    modules = models.CharField(max_length = 50, null = True, default = '')
    core_elective = models.CharField(max_length = 50, null = True, default = '')
    coursecode = models.CharField(max_length = 20, null = True, default = '')
    season = models.CharField(max_length = 20, null = True, default = '')
    year = models.CharField(max_length = 10, null = True, default = '')
    class_start = models.DateTimeField(null = True)
    class_end = models.DateTimeField(null = True)
    lecture = models.CharField(max_length = 100, null = True, default = '')
    textbook = models.TextField(null = True)
    venue = models.CharField(max_length = 50, null = True, default = '')
    attoch = models.CharField(max_length = 50, null = True, default = '')
    class_date3 = models.DateTimeField(null = True)
    class_date4 = models.DateTimeField(null = True)
    class_date5 = models.DateTimeField(null = True)
    is_removed = models.SmallIntegerField(max_length = 1, default = 0)
    class Meta:
        db_table = 'project_course_info'


"""
CREATE TABLE `project_course_info` (
  `id` int(11) NOT NULL auto_increment,
  `project_id` int(11) default '0',
  `period` varchar(20) collate utf8_bin default '',
  `modules` varchar(50) collate utf8_bin default '',
  `core_elective` varchar(50) collate utf8_bin default '',
  `coursecode` varchar(20) collate utf8_bin default '',
  `season` varchar(20) collate utf8_bin default '',
  `year` varchar(10) collate utf8_bin default '',
  `class_start` varchar(20) collate utf8_bin default '',
  `class_end` varchar(20) collate utf8_bin default '',
  `lecture` varchar(100) collate utf8_bin default '',
  `textbook` varchar(100) collate utf8_bin default '',
  `venue` varchar(50) collate utf8_bin default '',
  `attoch` varchar(50) collate utf8_bin default '',
  PRIMARY KEY  (`id`),
  KEY `project_id` (`project_id`),
  KEY `period` (`period`),
  KEY `coursecode` (`coursecode`),
  KEY `modules` (`modules`),
  KEY `class_start` (`class_start`),
  KEY `class_end` (`class_end`),
  KEY `venue` (`venue`),
  KEY `season` (`season`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 



CREATE TABLE `next_follow_date` (
  `id` int(11) NOT NULL auto_increment,
  `member_id` int(11) default '0',
  `follow_date` date default NULL,
  `remark` text collate utf8_bin,
  `is_deal` smallint(1) default '0',
  `is_removed` smallint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `member_id` (`member_id`),
  KEY `follow_date` (`follow_date`),
  KEY `is_removed` (`is_removed`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

"""